﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccessLayer
{
    public class ReqHistoryDAL
    {
        StationeryInventoryEntities1 ent;

        public ReqHistoryDAL()
        {
            ent = new StationeryInventoryEntities1();
        }

        public object checkingHeadOrNot(String userName) {
            object obj = new object();

            obj = (from u in ent.Users
                          where u.UserName == userName
                          select u.RoleID).SingleOrDefault();

            return obj;
        }

        public List<Employee> getEmployeeListByDepID(String userName)
        {
            List<Employee> obj = new List<Employee>();

            Employee e = ent.Employees.FirstOrDefault(x => x.User.UserName == userName);
            var EmpList = (from x in ent.Employees where x.DeptID == e.DeptID && x.User.UserName != userName select x).ToList();
            obj = EmpList.ToList();
            return obj;
        }

        public List<Requisition> getAllRequisitionByDept(String userName)
        {
            List<Requisition> allReqList = new List<Requisition>();
            int roleID = (from u in ent.Users
                          where u.UserName == userName
                          select u.RoleID).SingleOrDefault();
            if (roleID == 4)
            {
                Employee e = ent.Employees.FirstOrDefault(x => x.User.UserName == userName);
                List<Employee> EmpList = (from x in ent.Employees where x.DeptID == e.DeptID select x).ToList();
                

                for (int i = 0; i < EmpList.Count; i++)
                {
                    int empId = EmpList[i].EmployeeID;
                    List<Requisition> reqList = (from x in ent.Requisitions where x.RequestedBy == empId
                                                 orderby x.ReqDate descending select x).ToList();
                    for (int j = 0; j < reqList.Count; j++)
                    {
                        allReqList.Add(reqList[j]);
                    }
                }
            }
            else {
                var empID = (from e in ent.Employees
                             where e.User.UserName == userName
                             select e.EmployeeID).SingleOrDefault();
                allReqList = (from r in ent.Requisitions
                           where r.RequestedBy == empID
                           orderby r.ReqID descending
                           select r).ToList();
            }
            return allReqList;
        }

        public List<object> getIndividualReqByHead(int eid)
        {
            List<object> obj = new List<object>();

            var req = (from r in ent.Requisitions
                   where r.RequestedBy == eid
                   select r);
            obj = req.ToList<object>();
            return obj;
        }

        public List<Requisition> getAllReqByDate(string userName, DateTime fromDate, DateTime toDate)
        {
            List<Requisition> allReqList = new List<Requisition>();

            Employee e = ent.Employees.FirstOrDefault(x => x.User.UserName == userName);
            List<Employee> EmpList = (from x in ent.Employees where x.DeptID == e.DeptID select x).ToList();


            for (int i = 0; i < EmpList.Count; i++)
            {
                int empId = EmpList[i].EmployeeID;
                List<Requisition> reqList = (from x in ent.Requisitions where x.RequestedBy == empId && x.ReqDate >= fromDate && x.ReqDate <= toDate
                                             orderby x.ReqDate descending select x).ToList();
                for (int j = 0; j < reqList.Count; j++)
                {
                    allReqList.Add(reqList[j]);
                }
            }
            return allReqList;
        }

        public List<Requisition> getAllRequisitionByEmpId(int eid, DateTime fromDate, DateTime toDate)
        {
            List<Employee> EmpList = (from x in ent.Employees where x.EmployeeID == eid select x).ToList();
            List<Requisition> allReqList = new List<Requisition>();

            for (int i = 0; i < EmpList.Count; i++)
            {
                int empId = EmpList[i].EmployeeID;
                List<Requisition> reqList = (from x in ent.Requisitions 
                                             where x.RequestedBy == empId
                                             && x.ReqDate >= fromDate
                                             && x.ReqDate <= toDate
                                             orderby x.ReqDate descending
                                             select x).ToList();
                for (int j = 0; j < reqList.Count; j++)
                {
                    allReqList.Add(reqList[j]);
                }
            }
            
            return allReqList;
        }

        public List<object> getAllReqByIndividualEmpWithDate(string userName, DateTime fromDate, DateTime toDate)
        {
            List<object> obj = new List<object>();

            var empID = (from e in ent.Employees
                         where e.User.UserName == userName
                         select e.EmployeeID).SingleOrDefault();
            var req = (from r in ent.Requisitions
                          where r.RequestedBy == empID && r.ReqDate >= fromDate && r.ReqDate <= toDate
                          orderby r.ReqDate descending
                          select r);
            obj = req.ToList<object>();
            return obj;
        }

        public List<object> getReqDetailOneByReqID(int reqID)
        {
            List<object> obj = new List<object>();

            var reqDetList = (from r in ent.RequisitionDetailOnes
                              join s in ent.Stationeries on r.ItemCode equals s.ItemCode
                              where r.ReqID == reqID 
                              select new { s.ItemName, r.RequestQty, r.ApprovalStatus});
            obj = reqDetList.ToList<object>();
            return obj;
        }

        public void deleteOneRequisition(int i)
        {
            List<RequisitionDetailOne> lstReqDet = (from x in ent.RequisitionDetailOnes
                                                    where x.ReqID == i
                                                    select x).ToList();
            foreach(RequisitionDetailOne r in lstReqDet)
            {
                ent.RequisitionDetailOnes.Remove(r);
            }
            Requisition q = ent.Requisitions.FirstOrDefault(f=>f.ReqID==i);
            ent.Requisitions.Remove(q);
            //ent.Requisitions.Remove(q);
            ent.SaveChanges();
        }

        public bool getRequisitionByStatus(int reqId)
        {
            try
            {
                var q = (from h in ent.Requisitions
                         where h.ApprovalStatus.Equals("pending") && h.ReqID.Equals(reqId)
                         select h).FirstOrDefault();
                if (q != null)
                {//pending
                    return true;
                }
                else
                {//approve
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}



